﻿<%@ WebHandler Language="C#" Class="ToolPrapareReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 生产准备-工装工具需求报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.5.16
 **/

public class ToolPrapareReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getBOMData":
                        r = getBOMData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,nvl(cci.fromworkcentername,wck.workcentername) workcentername,cci.mfgordername,cci.productname,cci.containername,cci.familylistnames,
       DECODE(cci.containertype,7,'单件加工',2,'批量加工',3,'带子序号') containertype,
       DECODE(cci.containerstatus,1,'在制',2,'关闭') containerstatus,cci.workflowstepname,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty containerqty,m.plannedstartdate mfgstartdate,
       cci.plannedstartdate containerstartdate,m.plannedcompletiondate mfgcompletedate,cci.plannedcompletiondate containercompletedate,
       s.specno,cci.ExpectMaterialDate,
       DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,
       ad.starttime,ad.endtime,t.toolname,t.description,t.drawingno,t.tooltype,t.qty needqty,NVL(tg.menge,0) availableQty,
       CASE WHEN NVL(tg.menge,0)>=NVL(t.qty,0) THEN '是' ELSE '否' END isqitao,nvl(rd.resourcename,wk.workstationname) resourcename,nvl(rd.description,wk.workstationname) resourcedescription
FROM apsplaninfo ai 
LEFT JOIN containercurrentinfo cci ON cci.containerid=ai.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN apsplandetail ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ad.workflowstepid 
LEFT JOIN Enginepartplan en ON nvl(en.containerid,en.concontainerid)=ai.containerid--add by longb 2023-10-19
LEFT JOIN Workcenter wck ON wck.workcenterid=en.workcenterid --add by longb 2023-10-19
LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
LEFT JOIN 
(
   SELECT SUM(qty) qty,ct.toolname,ct.description,drawingno,rt.resourcetypename,t.workflowstepid,ct.tooltype
   FROM 
   (
        SELECT t.qty, t.sequence,t.tools,t.workflowstepid 
        FROM workflowsteptools t
        LEFT JOIN workflowstep ws ON ws.workflowstepid=t.workflowstepid 
        UNION ALL
        SELECT t.qty, t.sequence,t.tools,sio.workflowstepid FROM stepinfotools t
        LEFT JOIN stepinfo sio ON sio.stepinfoid = t.stepinfoid
        LEFT JOIN workflowstep ws ON ws.workflowstepid=sio.workflowstepid 
   ) t
   LEFT JOIN 
   (
        SELECT specialtoolname toolname,DESCRIPTION,specialtoolid toolid,resourcetypeid,drawingno,'夹模具' tooltype FROM specialtool
        UNION ALL
        SELECT mtt.measuretoolname toolname,mtt.description,mtt.measuretoolid toolid,mtt.resourcetypeid,mtt.drawingno,'量具' tooltype FROM measuretool mtt
        UNION ALL 
        SELECT ctt.cuttoolname toolname,ctt.description,ctt.cuttoolid toolid,ctt.resourcetypeid,ctt.drawingno,'刀具' tooltype FROM cuttool ctt
        UNION ALL 
        SELECT aux.auxmaterialname toolname,aux.description,aux.auxmaterialid toolid,aux.resourcetypeid,aux.vendormodel drawingno,'辅材' tooltype 
        FROM auxmaterial aux
   ) ct ON ct.toolid = t.tools
   LEFT JOIN resourcetype rt ON rt.resourcetypeid = ct.resourcetypeid 
   WHERE ct.toolid IS NOT NULL 
   GROUP BY ct.toolname,ct.description,ct.drawingno,rt.resourcetypename,t.workflowstepid,ct.tooltype
) t ON t.workflowstepid=ad.workflowstepid
LEFT JOIN (SELECT SUM(tg.menge) menge,tg.zeinr FROM t_ggdkc tg  GROUP BY tg.zeinr) tg ON tg.zeinr=drawingno
LEFT JOIN ContainerStepNoShip csn ON csn.containerid = cci.containerid AND csn.workflowstepid = wfs.workflowstepid AND csn.isuse = 0 AND csn.noship = 2
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = wfs.workflowstepid
LEFT JOIN resourcedef rd ON rd.resourceid = ad.resourceid
LEFT JOIN Workstation wk ON wk.workstationid=ad.resourceid
WHERE ai.isuse=0  AND ai.apstype2 = 2 AND NVL(t.qty,0) > 0 AND csd.reportdate IS NULL ";
        //装配厂 add by longb 2023-10-19 
        if (jObject["factoryid"].ToString() != "0004e280000026e5")
        {
            sql += " AND ai.apstype=1 AND csn.containerstepnoshipid IS NULL ";
        }

        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND nvl(cci.fromworkcenterid,en.workcenterid) = '{0}'", jObject["workcenterid"].ToString());//--modify by longb 2023-10-19
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        // 工装工具类型
        if (jObject["tooltype"] != null && jObject["tooltype"].ToString() != "")
        {
            sql += string.Format(" AND t.tooltype = '{0}'", jObject["tooltype"].ToString());
        }

        // 工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND s.specno LIKE '%{0}%'", jObject["specno"].ToString());
        }

        // 工序名称
        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND DECODE(sb.specname,NULL,s.description,sb.specname) LIKE '%{0}%'", jObject["specname"].ToString());
        }

        // 工序计划开始时间
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        // 工序计划完成时间
        if (jObject["endtime"] != null && jObject["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["endtime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        // 手动指定日期 add by longb 2024-02-27
        if (jObject["realstartdate"] != null && jObject["realstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["realstartdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cci.ExpectMaterialDate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cci.ExpectMaterialDate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        // 是否齐套
        if (jObject["isqitao"] != null && jObject["isqitao"].ToString() != "")
        {
            if (jObject["isqitao"].ToString() == "是")
            {
                sql += " AND NVL(tg.menge,0) >= NVL(t.qty,0)";
            }
            else
            {
                sql += " AND NVL(tg.menge,0) < NVL(t.qty,0)";
            }
        }
        
        //编号
        if (jObject["toolname"] != null && jObject["toolname"].ToString() != "")
        {
            sql += string.Format(" AND t.toolname = '{0}'", jObject["toolname"].ToString());
        }

        //工具图号
        if (jObject["drawingno"] != null && jObject["drawingno"].ToString() != "")
        {
            sql += string.Format(" AND t.drawingno = '{0}'", jObject["drawingno"].ToString());
        }
        return sql;
    }

    string getQueryBOMSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,cci.fromworkcentername workcentername,cci.mfgordername,cci.productname,cci.containername,cci.familylistnames,
       DECODE(cci.containertype,7,'单件加工',2,'批量加工',3,'带子序号') containertype,
       DECODE(cci.containerstatus,1,'在制',2,'关闭') containerstatus,cci.workflowstepname,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty containerqty,m.plannedstartdate mfgstartdate,
       cci.plannedstartdate containerstartdate,m.plannedcompletiondate mfgcompletedate,cci.plannedcompletiondate containercompletedate,
       s.specno,cci.ExpectMaterialDate,
       DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,
       ad.starttime,ad.endtime,
       pb.productname bproductname,pb.productname tproductname,p.description,pt.producttypename,pm.qtyrequired needqty,'' availableQty,'' isqitao,
       rd.resourcename,rd.description resourcedescription
FROM apsplaninfo ai 
LEFT JOIN apsplandetail ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN containercurrentinfo cci ON cci.containerid=ad.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ad.workflowstepid 
INNER JOIN productmateriallistitem pm ON pm.bomid=cci.bomid
LEFT JOIN specbase sb1 ON sb1.specbaseid=pm.specbaseid
INNER JOIN spec s ON s.specid=NVL(sb1.revofrcdid,pm.specid) and s.specid = wfs.specid
LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid
LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid   
LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid)  
LEFT JOIN ProductType pt ON pt.ProductTypeID=p.ProductTypeID
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid  
LEFT JOIN bom b ON b.bomid = pm.bomid
LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid 
LEFT JOIN ContainerStepNoShip csn ON csn.containerid = cci.containerid AND csn.workflowstepid = wfs.workflowstepid AND csn.isuse = 0 AND csn.noship = 2
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = wfs.workflowstepid
LEFT JOIN resourcedef rd ON rd.resourceid = ad.resourceid
WHERE ai.isuse=0 AND ai.apstype=1 AND ai.apstype2 = 2 AND csd.reportdate IS NULL AND csn.containerstepnoshipid IS NULL ";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //// 工装工具类型
        //if (jObject["tooltype"] != null && jObject["tooltype"].ToString() != "")
        //{
        //    sql += string.Format(" AND t.tooltype = '{0}'", jObject["tooltype"].ToString());
        //}

        // 工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND s.specno LIKE '%{0}%'", jObject["specno"].ToString());
        }

        // 工序名称
        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND DECODE(sb.specname,NULL,s.description,sb.specname) LIKE '%{0}%'", jObject["specname"].ToString());
        }

        // 工序计划开始时间
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        // 工序计划完成时间
        if (jObject["endtime"] != null && jObject["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["endtime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }
        
        // 手动指定日期 add by longb 2024-02-27
        if (jObject["realstartdate"] != null && jObject["realstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["realstartdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cci.ExpectMaterialDate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cci.ExpectMaterialDate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }
        //// 是否齐套
        //if (jObject["isqitao"] != null && jObject["isqitao"].ToString() != "")
        //{
        //    if (jObject["isqitao"].ToString() == "是")
        //    {
        //        sql += " AND NVL(tg.menge,0) >= NVL(t.qty,0)";
        //    }
        //    else
        //    {
        //        sql += " AND NVL(tg.menge,0) < NVL(t.qty,0)";
        //    }
        //}
        return sql;
    }

    string getQueryZSBOMSql(JObject jObject)
    {
        string sql = @"
SELECT DISTINCT cci.bomid,en.machineno,f.factoryname,nvl(cci.fromworkcentername,wck.workcentername) workcentername,cci.mfgordername,cci.productname,cci.containername,nvl(m.ProductFamilyName,pf.productfamilyname) familylistnames,
       DECODE(cci.containertype,7,'单件加工',2,'批量加工',3,'带子序号') containertype,
       DECODE(cci.containerstatus,1,'在制',2,'关闭') containerstatus,cci.workflowstepname,
       cci.workflowname || ':' || cci.workflowrevision workflowinfo,cci.qty containerqty,m.plannedstartdate mfgstartdate,
       cci.plannedstartdate containerstartdate,m.plannedcompletiondate mfgcompletedate,cci.plannedcompletiondate containercompletedate,
       s.specno,cci.ExpectMaterialDate,
       DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,
       ad.starttime,ad.endtime,
       pb.productname bproductname,pb.productname tproductname,p.description,pt.producttypename,pm.qtyrequired needqty,'' availableQty,'' isqitao,
       rd.workstationname resourcename,rd.workstationname resourcedescription
FROM apsplaninfo ai 
LEFT JOIN apsplandetail ad ON ad.apsplaninfoid = ai.apsplaninfoid AND ad.isuse=0
LEFT JOIN containercurrentinfo cci ON cci.containerid=ad.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder m on m.mfgorderid = cci.mfgorderid
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ad.workflowstepid
LEFT JOIN Enginepartplan en ON nvl(en.containerid,en.concontainerid)=ai.containerid
LEFT JOIN Workcenter wck ON wck.workcenterid=en.workcenterid 
LEFT JOIN spec s ON s.specid = wfs.specid 
LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid
INNER JOIN productmateriallistitem pm ON pm.bomid=cci.bomid AND pm.specid=s.specid
LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid   
LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid)  
LEFT JOIN ProductType pt ON pt.ProductTypeID=p.ProductTypeID
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid  
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = wfs.workflowstepid
LEFT JOIN workstation rd ON rd.workstationid = ad.resourceid
LEFT JOIN workflowproductfamilylist wff ON wff.workflowid=nvl(wfs.workflowid,en.workflowid)
LEFT JOIN productfamily pf ON pf.productfamilyid=wff.productfamilylistid
WHERE ai.isuse=0 AND ai.apstype2 = 2 AND csd.reportdate IS NULL";//AND ai.apstype=1
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND en.workcenterid  = '{0}'", jObject["workcenterid"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        // 工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND s.specno LIKE '%{0}%'", jObject["specno"].ToString());
        }

        // 工序名称
        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND DECODE(sb.specname,NULL,s.description,sb.specname) LIKE '%{0}%'", jObject["specname"].ToString());
        }

        // 工序计划开始时间
        if (jObject["starttime"] != null && jObject["starttime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["starttime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.starttime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.starttime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        // 工序计划完成时间
        if (jObject["endtime"] != null && jObject["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["endtime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND ad.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND ad.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }
        // 手动指定日期 add by longb 2024-02-27
        if (jObject["realstartdate"] != null && jObject["realstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["realstartdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cci.ExpectMaterialDate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cci.ExpectMaterialDate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询方法
    Result getBOMData(JObject jObject)
    {
        string sql = getQueryBOMSql(jObject);
        if (jObject["factoryid"].ToString() == "0004e280000026e5")//add by longb 2023-10-19 新增装配bom取值逻辑
        {
            sql = getQueryZSBOMSql(jObject);
        }
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {

        DataTable dt = null;
        if (jObject["tooltype"].ToString() == "BOM")
        {
            dt = dal.GetDataTable(getQueryBOMSql(jObject));
        }
        else
        {
            dt = dal.GetDataTable(getQuerySql(jObject));
        }
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dt.Columns["factoryname"].Caption = "主制分厂";
        dt.Columns["workcentername"].Caption = "工区";
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["productname"].Caption = "零件号";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["containertype"].Caption = "批次类型";
        dt.Columns["containerstatus"].Caption = "批次状态";
        dt.Columns["workflowstepname"].Caption = "当前工序";
        dt.Columns["workflowinfo"].Caption = "工艺及版次";
        dt.Columns["mfgstartdate"].Caption = "订单要求开始";
        dt.Columns["containerstartdate"].Caption = "批次计划开始";
        dt.Columns["mfgcompletedate"].Caption = "订单要求完成";
        dt.Columns["containercompletedate"].Caption = "批次计划完成";
        dt.Columns["expectmaterialdate"].Caption = "手动指定开始";
        dt.Columns["specno"].Caption = "工序号";
        dt.Columns["specname"].Caption = "工序名称";
        dt.Columns["starttime"].Caption = "工序计划开始";
        dt.Columns["endtime"].Caption = "工序计划完成";
        if (jObject["tooltype"].ToString() == "BOM")
        {
            dt.Columns["bproductname"].Caption = "编号";
            dt.Columns["description"].Caption = "零件名称";
            dt.Columns["producttypename"].Caption = "零件类型";
            dt.Columns["tproductname"].Caption = "零件图号";
        }
        else
        {
            dt.Columns["toolname"].Caption = "编号";
            dt.Columns["description"].Caption = "工具/辅材名称";
            dt.Columns["tooltype"].Caption = "工具类型";
            dt.Columns["drawingno"].Caption = "工具图号";
        }
        dt.Columns["needqty"].Caption = "需求数量";
        dt.Columns["availableqty"].Caption = "可用数";
        dt.Columns["isqitao"].Caption = "是否齐套";
        dt.Columns["resourcename"].Caption = "设备编号";
        dt.Columns["resourcedescription"].Caption = "设备名称";
        ExportHelper.ExportExcel(dt, "生产准备-工装工具需求报表");
    }
}